# -*- coding: utf-8 -*-
"""
推荐系统数据访问层

负责处理商品推荐相关的数据查询
"""
from typing import List, Dict, Any, Optional
from app.repositories.base_repository import BaseRepository


class RecommendationRepository(BaseRepository):
    """推荐系统数据访问类"""
    
    def __init__(self):
        super().__init__("goods")
    
    def get_user_purchase_history(self, user_id: str, limit: int = 50) -> List[Dict[str, Any]]:
        """获取用户购买历史"""
        sql = """
            SELECT DISTINCT
                tog.goods_id,
                tog.goods_name,
                g.class_name,
                g.brand_name,
                COUNT(*) as purchase_count,
                MAX(to.created) as last_purchase_time
            FROM trade_order_goods tog
            INNER JOIN trade_orders tor ON tog.tid = tor.tid
            INNER JOIN goods g ON tog.goods_id = g.goods_id
            WHERE tor.buyer_id = %s 
                AND tor.status IN ('WAIT_SELLER_SEND_GOODS', 'WAIT_BUYER_CONFIRM_GOODS', 'TRADE_FINISHED')
                AND g.deleted != '1'
            GROUP BY tog.goods_id, tog.goods_name, g.class_name, g.brand_name
            ORDER BY purchase_count DESC, last_purchase_time DESC
            LIMIT %s
        """
        return self.execute_custom_query(sql, [user_id, limit], True) or []
    
    def get_user_cart_categories(self, user_id: str) -> List[Dict[str, Any]]:
        """获取用户购物车中的商品类别"""
        sql = """
            SELECT DISTINCT
                g.class_name,
                g.brand_name,
                COUNT(*) as count
            FROM shopping_cart sc
            INNER JOIN goods g ON sc.goods_id = g.goods_id
            WHERE sc.user_id = %s AND g.deleted != '1'
            GROUP BY g.class_name, g.brand_name
            ORDER BY count DESC
        """
        return self.execute_custom_query(sql, [user_id], True) or []
    
    def get_goods_by_category(self, class_name: str, brand_name: Optional[str] = None, 
                             exclude_goods_ids: List[str] = None, limit: int = 10) -> List[Dict[str, Any]]:
        """根据类别获取商品"""
        where_conditions = ["g.class_name = %s", "g.deleted != '1'"]
        params = [class_name]
        
        if brand_name:
            where_conditions.append("g.brand_name = %s")
            params.append(brand_name)
        
        if exclude_goods_ids:
            placeholders = ','.join(['%s'] * len(exclude_goods_ids))
            where_conditions.append(f"g.goods_id NOT IN ({placeholders})")
            params.extend(exclude_goods_ids)
        
        sql = f"""
            SELECT 
                g.goods_id,
                g.goods_no,
                g.goods_name,
                g.class_name,
                g.brand_name,
                gs.spec_id,
                gs.spec_name,
                gs.retail_price,
                gs.img_url,
                COALESCE(stock.available_stock, 0) as available_stock
            FROM goods g
            INNER JOIN goods_spec gs ON g.goods_id = gs.goods_id
            LEFT JOIN goods_stock stock ON g.goods_id = stock.goods_id AND gs.spec_id = stock.spec_id
            WHERE {' AND '.join(where_conditions)}
            ORDER BY g.created_at DESC, gs.retail_price ASC
            LIMIT %s
        """
        params.append(limit)
        return self.execute_custom_query(sql, params, True) or []
    
    def get_popular_goods(self, exclude_goods_ids: List[str] = None, limit: int = 10) -> List[Dict[str, Any]]:
        """获取热门商品（基于购买次数）"""
        where_conditions = ["g.deleted != '1'"]
        params = []
        
        if exclude_goods_ids:
            placeholders = ','.join(['%s'] * len(exclude_goods_ids))
            where_conditions.append(f"g.goods_id NOT IN ({placeholders})")
            params.extend(exclude_goods_ids)
        
        sql = f"""
            SELECT 
                g.goods_id,
                g.goods_no,
                g.goods_name,
                g.class_name,
                g.brand_name,
                gs.spec_id,
                gs.spec_name,
                gs.retail_price,
                gs.img_url,
                COALESCE(stock.available_stock, 0) as available_stock,
                COALESCE(sales.total_sales, 0) as total_sales
            FROM goods g
            INNER JOIN goods_spec gs ON g.goods_id = gs.goods_id
            LEFT JOIN goods_stock stock ON g.goods_id = stock.goods_id AND gs.spec_id = stock.spec_id
            LEFT JOIN (
                SELECT 
                    goods_id,
                    COUNT(*) as total_sales,
                    SUM(CAST(num AS UNSIGNED)) as total_quantity
                FROM trade_order_goods tog
                INNER JOIN trade_orders tor ON tog.tid = tor.tid
                WHERE tor.status IN ('WAIT_SELLER_SEND_GOODS', 'WAIT_BUYER_CONFIRM_GOODS', 'TRADE_FINISHED')
                GROUP BY goods_id
            ) sales ON g.goods_id = sales.goods_id
            WHERE {' AND '.join(where_conditions)}
            ORDER BY COALESCE(sales.total_sales, 0) DESC, g.created_at DESC
            LIMIT %s
        """
        params.append(limit)
        return self.execute_custom_query(sql, params, True) or []
    
    def get_goods_by_brand(self, brand_name: str, exclude_goods_ids: List[str] = None, 
                          limit: int = 10) -> List[Dict[str, Any]]:
        """根据品牌获取商品"""
        where_conditions = ["g.brand_name = %s", "g.deleted != '1'"]
        params = [brand_name]
        
        if exclude_goods_ids:
            placeholders = ','.join(['%s'] * len(exclude_goods_ids))
            where_conditions.append(f"g.goods_id NOT IN ({placeholders})")
            params.extend(exclude_goods_ids)
        
        sql = f"""
            SELECT 
                g.goods_id,
                g.goods_no,
                g.goods_name,
                g.class_name,
                g.brand_name,
                gs.spec_id,
                gs.spec_name,
                gs.retail_price,
                gs.img_url,
                COALESCE(stock.available_stock, 0) as available_stock
            FROM goods g
            INNER JOIN goods_spec gs ON g.goods_id = gs.goods_id
            LEFT JOIN goods_stock stock ON g.goods_id = stock.goods_id AND gs.spec_id = stock.spec_id
            WHERE {' AND '.join(where_conditions)}
            ORDER BY g.created_at DESC, gs.retail_price ASC
            LIMIT %s
        """
        params.append(limit)
        return self.execute_custom_query(sql, params, True) or []
    
    def get_similar_goods(self, goods_id: str, limit: int = 10) -> List[Dict[str, Any]]:
        """获取相似商品（同类别同品牌）"""
        sql = """
            SELECT 
                similar_g.goods_id,
                similar_g.goods_no,
                similar_g.goods_name,
                similar_g.class_name,
                similar_g.brand_name,
                gs.spec_id,
                gs.spec_name,
                gs.retail_price,
                gs.img_url,
                COALESCE(stock.available_stock, 0) as available_stock
            FROM goods base_g
            INNER JOIN goods similar_g ON (
                similar_g.class_name = base_g.class_name 
                OR similar_g.brand_name = base_g.brand_name
            )
            INNER JOIN goods_spec gs ON similar_g.goods_id = gs.goods_id
            LEFT JOIN goods_stock stock ON similar_g.goods_id = stock.goods_id AND gs.spec_id = stock.spec_id
            WHERE base_g.goods_id = %s 
                AND similar_g.goods_id != %s
                AND similar_g.deleted != '1'
            ORDER BY 
                CASE 
                    WHEN similar_g.class_name = base_g.class_name AND similar_g.brand_name = base_g.brand_name THEN 1
                    WHEN similar_g.class_name = base_g.class_name THEN 2
                    WHEN similar_g.brand_name = base_g.brand_name THEN 3
                    ELSE 4
                END,
                similar_g.created_at DESC
            LIMIT %s
        """
        return self.execute_custom_query(sql, [goods_id, goods_id, limit], True) or []
    
    def get_recently_viewed_goods(self, user_id: str, limit: int = 20) -> List[Dict[str, Any]]:
        """获取用户最近浏览的商品（基于购物车历史）"""
        sql = """
            SELECT DISTINCT
                g.goods_id,
                g.goods_no,
                g.goods_name,
                g.class_name,
                g.brand_name,
                gs.spec_id,
                gs.spec_name,
                gs.retail_price,
                gs.img_url,
                COALESCE(stock.available_stock, 0) as available_stock,
                MAX(sc.created_time) as last_view_time
            FROM shopping_cart sc
            INNER JOIN goods g ON sc.goods_id = g.goods_id
            INNER JOIN goods_spec gs ON sc.spec_id = gs.spec_id
            LEFT JOIN goods_stock stock ON g.goods_id = stock.goods_id AND gs.spec_id = stock.spec_id
            WHERE sc.user_id = %s AND g.deleted != '1'
            GROUP BY g.goods_id, g.goods_no, g.goods_name, g.class_name, g.brand_name,
                     gs.spec_id, gs.spec_name, gs.retail_price, gs.img_url, stock.available_stock
            ORDER BY last_view_time DESC
            LIMIT %s
        """
        return self.execute_custom_query(sql, [user_id, limit], True) or []
    
    def get_recommendations_by_category(self, user_id: str, limit: int = 10):
        """根据分类推荐商品 - 修复字段名"""
        query = """
        SELECT DISTINCT
            g.goods_id,
            g.goods_no,
            g.goods_name,
            g.class_name,  # 修复：category_name -> class_name
            g.brand_name,
            gs.spec_id,
            gs.spec_name,
            gs.retail_price,
            gs.img_url,
            COALESCE(stock.available_stock, 0) as available_stock
        FROM goods g
        INNER JOIN goods_spec gs ON g.goods_id = gs.goods_id
        LEFT JOIN goods_stock stock ON g.goods_id = stock.goods_id AND gs.spec_id = stock.spec_id
        WHERE g.class_id = %s AND g.deleted != '1'  # 修复：category_id -> class_id
        ORDER BY g.created_at DESC, gs.retail_price ASC
        LIMIT %s
        """
        return self.execute_query(query, (user_id, limit))
    
    def execute_custom_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行自定义查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor(dictionary=True)
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchall()
            cursor.close()
            connection.close()
            
            return result if result else []
        except Exception as e:
            print(f"查询错误: {e}")
            return []
    
    def execute_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行查询"""
        return self.execute_custom_query(query, params, *args, **kwargs)
    
    def count_query(self, query: str, params: tuple = None, *args, **kwargs):
        """计数查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchone()
            cursor.close()
            connection.close()
            
            return result[0] if result else 0
        except Exception as e:
            print(f"计数错误: {e}")
            return 0
